/**
 * AddTakenCompanyInfoDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.CompanyDTO;
import pits.dto.DepartexpDTO;
import pits.form.AddTakenCompanyInfoForm;
import pits.util.Constants;

/**
 * @author ltd
 * 
 */
public class AddTakenCompanyInfoDAO {

	public List<CompanyDTO> getComps() {
		final List<CompanyDTO> result = new ArrayList<CompanyDTO>();
		String query = Constants.BLANK_VALUE;
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pStat = null;
		CompanyDTO dto = null;

		try {
			conn = pits.util.Connection.getConnection();
			query = "select ComName, CompanyID " + "from company ";
			pStat = conn.prepareStatement(query);
			rs = pStat.executeQuery();
			while (rs.next()) {
				dto = new CompanyDTO();
				dto.setComName(rs.getString("ComName"));
				dto.setCompanyID(rs.getInt("CompanyID"));
				result.add(dto);
			}
		} catch (final Exception sqlEx) {
			// log.error("EditTakenCompanyInfoForm.getDepartexps " + sqlEx);
		} finally {
			try {
				rs.close();
				pStat.close();
				conn.close();
			} catch (final SQLException sqlEx) {
				// log.error("EditTakenCompanyInfoForm.getDepartexps " + sqlEx);
			} catch (final Exception ex) {
				// log.error("EditTakenCompanyInfoForm.getDepartexps " +
				// ex.getStackTrace());
			}
		}

		return result;
	}

	public List<DepartexpDTO> addDepartexp(int empID, AddTakenCompanyInfoForm form) {
		List<DepartexpDTO> result = new ArrayList<DepartexpDTO>();
		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		PreparedStatement pStat = null;
		final EditTakenCompanyInfoDAO dao = new EditTakenCompanyInfoDAO();
		ResultSet rs = null;
		// int departID = -1;
		int roleID = -1;

		try {
			conn = pits.util.Connection.getConnection();
			/*
			 * query = "select DepartID " + "from department " +
			 * "where Departname=? "; pStat = conn.prepareStatement(query);
			 * pStat.setString(1, form.getTxtDepartment()); rs =
			 * pStat.executeQuery(); while (rs.next()) { departID =
			 * rs.getInt("DepartID"); }
			 */
			query = "select RoleID " + "from role " + "where Role=? ";
			pStat = conn.prepareStatement(query);
			pStat.setString(1, form.getTxtRole());
			rs = pStat.executeQuery();
			while (rs.next()) {
				roleID = rs.getInt("RoleID");
			}
			if (roleID != -1) {
				query = "insert ignore departexp " + "values(?, ?, ?, ?, ?, ?, 0) ";
				pStat = conn.prepareStatement(query);
				pStat.setInt(1, Integer.parseInt(form.getTxtDepartID()));
				pStat.setInt(2, empID);
				pStat.setString(3, form.getTxtStartDate());
				pStat.setString(4, form.getTxtEndDate());
				pStat.setInt(5, roleID);
				pStat.setString(6, form.getTxtDescription());
				pStat.executeUpdate();
				new pits.util.Logger().write(pStat.toString());
				result = dao.getDepartexps(empID);
			} else {
				// log.error
			}
		} catch (final Exception sqlEx) {
			new pits.util.Logger().writeln("EditTakenCompanyInfoForm.getDepartexps " + sqlEx);
		} finally {
			try {
				pStat.close();
				conn.close();
			} catch (final SQLException sqlEx) {
				new pits.util.Logger().writeln("EditTakenCompanyInfoForm.getDepartexps " + sqlEx);
			} catch (final Exception ex) {
				new pits.util.Logger().writeln("EditTakenCompanyInfoForm.getDepartexps " + ex.getStackTrace());
			}
		}

		return result;
	}
}
